package service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import models.Fornecedor;
import play.db.DB;
import exceptions.BusinessException;

public class FornecedorDAO {

	private Connection connection = null;
	private Statement statement = null;
	private ResultSet resultSet = null;

	public void cadastrarFornecedor(Fornecedor fornecedor) throws SQLException,
			BusinessException {

		connection = DB.getConnection();

		statement = connection.createStatement();

		StringBuffer query = new StringBuffer();

		query.append("SELECT * from FORNECEDOR WHERE CNPJ = '"
				+ fornecedor.cnpj + "';");

		resultSet = statement.executeQuery(query.toString());
		Fornecedor bdFornecedor = writeResultSet(resultSet);

		if (bdFornecedor == null) {
			query = new StringBuffer();
			query.append("INSERT INTO FORNECEDOR(DSFORNECEDOR,CNPJ,EMAIL) VALUES (");
			query.append("'" + fornecedor.nomeFornecedor + "',");
			query.append("'" + fornecedor.cnpj + "',");
			query.append("'" + fornecedor.email + "'");
			query.append(");");
			System.out.println(query.toString());

			statement.execute(query.toString());
		} else {
			throw new BusinessException();
		}
	}

	public List<Fornecedor> buscarTodosFornecedores() throws SQLException, BusinessException {
		connection = DB.getConnection();

		statement = connection.createStatement();

		StringBuffer query = new StringBuffer();

		query.append("SELECT * from FORNECEDOR;");

		resultSet = statement.executeQuery(query.toString());
		List<Fornecedor> bdFornecedor = writeResultSetList(resultSet);

		return bdFornecedor;
		
	}

	private Fornecedor writeResultSet(ResultSet resultSet) throws SQLException {
		// resultSet is initialised before the first data set
		Fornecedor fornecedor = null;
		while (resultSet.next()) {
			fornecedor = new Fornecedor();
			int id = resultSet.getInt("IDFORNECEDOR");
			String nome = resultSet.getString("DSFORNECEDOR");
			String cnpj = resultSet.getString("CNPJ");
			String email = resultSet.getString("EMAIL");

			fornecedor.id = id;
			fornecedor.nomeFornecedor = nome;
			fornecedor.cnpj = cnpj;
			fornecedor.email = email;
		}

		return fornecedor;
	}
	
	private List<Fornecedor> writeResultSetList(ResultSet resultSet)
			throws SQLException {
		// resultSet is initialised before the first data set
		Fornecedor fornecedor = null;

		List<Fornecedor> result = new ArrayList<Fornecedor>();

		while (resultSet.next()) {
			fornecedor = new Fornecedor();
			int id = resultSet.getInt("IDFORNECEDOR");
			String nome = resultSet.getString("DSFORNECEDOR");
			String cnpj = resultSet.getString("CNPJ");
			String email = resultSet.getString("EMAIL");

			fornecedor.id = id;
			fornecedor.nomeFornecedor = nome;
			fornecedor.cnpj = cnpj;
			fornecedor.email = email;
			result.add(fornecedor);
		}
		if (result.size() == 0) {
			return null;
		}
		return result;
	}

}
